/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package javaapplication1;

/**
 *
 * @author abosancic
 */
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class Test {

    public static void main(String[] args) throws Throwable {
        Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "aco");
        DatabaseMetaData meta = conn.getMetaData();
        String schemaName = "test";
        List<String> tables = listAllTableNames(meta, schemaName);
        //List<ReferenceModel> model = printForeignKeys(schemaName, tables.iterator(), meta);
        
//        for (ReferenceModel referenceModel : model) {
//            System.out.println("ref: " + referenceModel);
//        }
        //printPrimaryKeys(schemaName, tables.iterator(), meta);
        //printUniqueIndexes(schemaName, tables.iterator(), meta);
    }

    private static List<String> listAllTableNames(DatabaseMetaData meta, String schemaName) throws SQLException {
        List<String> list = new ArrayList<String>();
        ResultSet rs = meta.getTables(null, schemaName, null, new String[] { "TABLE" });
        while(rs.next()) {
            String tableName = rs.getString("TABLE_NAME");
            list.add(tableName);
        }
        return list;
    }

    private static void printPrimaryKeys(String schemaName, Iterator<String> iter, DatabaseMetaData meta) throws SQLException {
        while(iter.hasNext()) {
            String tableName = iter.next();
            ResultSet rs = meta.getPrimaryKeys(null, schemaName, tableName);
            while(rs.next()) {
                String pkName = rs.getString("PK_NAME");
                String table = rs.getString("TABLE_NAME");
                String schema = rs.getString("TABLE_SCHEM");
                String columnName = rs.getString("COLUMN_NAME");
                System.out.println("----------------------------------------");
                System.out.println("Table: " + schema + "." + table);
                System.out.println("Key Name: " + pkName);
                System.out.println("Column(s): " + columnName);
            }
        }       
    }
    
//    private static List<ReferenceModel> printForeignKeys(String schemaName, Iterator<String> iter, DatabaseMetaData meta) throws SQLException {
//        List<String> tables = new ArrayList<>();
//        
//        List<ReferenceModel> model = new ArrayList<>();
//        
//        while (iter.hasNext()) {
//            String tableName = iter.next();
//            System.out.println("table: " + tableName);
//            tables.add(tableName);
//        }
//        
//        Iterator<String> itert = tables.iterator();
//        while (itert.hasNext()) {
//            String tableName = itert.next();
//            Iterator<String> inneriter = tables.iterator();
//            
//            while (inneriter.hasNext()) {
//                String innerTableName = inneriter.next();
//                ResultSet rs = meta.getCrossReference(null, null, tableName, null, null, innerTableName);
//                //System.out.println(String.format("Exported Keys Info Table %s.", tableName));
//                while (rs.next()) {
//
//                    String pkey = rs.getString("PKCOLUMN_NAME");
//                    String ptab = rs.getString("PKTABLE_NAME");
//                    String fkey = rs.getString("FKCOLUMN_NAME");
//                    String ftab = rs.getString("FKTABLE_NAME");
////                    System.out.println("primary key table = " + ptab);
////                    System.out.println("primary key = " + pkey);
////                    System.out.println("foreign key table = " + ftab);
////                    System.out.println("foreign key = " + fkey);
//                    
//                    model.add(new ReferenceModel(pkey, ptab, fkey, ftab));
//                    
//                }
//                
//                ResultSet rs1 = meta.getCrossReference(null, null, innerTableName, null, null, tableName);
//                //System.out.println(String.format("Exported Keys Info Table %s.", innerTableName));
//                while (rs.next()) {
//
//                    String pkey = rs.getString("PKCOLUMN_NAME");
//                    String ptab = rs.getString("PKTABLE_NAME");
//                    String fkey = rs.getString("FKCOLUMN_NAME");
//                    String ftab = rs.getString("FKTABLE_NAME");
////                    System.out.println("primary key table = " + ptab);
////                    System.out.println("primary key = " + pkey);
////                    System.out.println("foreign key table = " + ftab);
////                    System.out.println("foreign key = " + fkey);
//                    model.add(new ReferenceModel(pkey, ptab, fkey, ftab));
//                }
//                
//            }
//        }
//        
//        return model;
//        
//    }

    private static void printUniqueIndexes(String schemaName, Iterator<String> iter, DatabaseMetaData meta) throws SQLException {
        while(iter.hasNext()) {
            String tableName = iter.next();
            boolean listUniqueIndex = true;
            ResultSet rs = meta.getIndexInfo(null, schemaName, tableName, listUniqueIndex, true);
            while(rs.next()) {
                String indexName = rs.getString("INDEX_NAME");
                String table = rs.getString("TABLE_NAME");
                String schema = rs.getString("TABLE_SCHEM");
                String columnName = rs.getString("COLUMN_NAME");
                if(indexName == null) {
                    continue;
                }
                System.out.println("****************************************");
                System.out.println("Table: " + schema + "." + table);
                System.out.println("Index Name: " + indexName);
                System.out.println("Column Name: " + columnName);
            }
        }
    }
}
